﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CMP.ERP.Model;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using CMP.ERP.Helper;
namespace CMP.ERP.DAL
{
    /// <summary>
    /// 商品信息表
    /// </summary>
    public class CommodityDAL
    { 
        //实例化SQLHelper对象
        SQLHelper p = new SQLHelper();

        string connSql = ConfigurationManager.ConnectionStrings["ConnMySql"].ToString();
        /// <summary>
        /// 获取商品信息
        /// </summary>
        /// <returns></returns>
        public List<Commodity> GetCommodityAll()
        {
            string sql = "select ct.CommodityType_Id,Commodity_Id,Commodity_Name,Commodity_number,Commodity_Price ,Commodity_Weight,Commodity_Remark,ct.CommodityType_Name from Commodity c join CommodityType ct on c.CommodityType_Id=ct.CommodityType_Id";
            SqlDataAdapter adapter = new SqlDataAdapter(sql,connSql);
            DataTable table = new DataTable();
            adapter.Fill(table);
            List<Commodity> list = new List<Commodity>();
            foreach (DataRow item in table.Rows)
            {
                Commodity c = new Commodity();
                c.Commodity_Id = Convert.ToInt32(item["Commodity_Id"]);
                c.Commodity_Name = (item["Commodity_Name"]).ToString();
                c.Commodity_number = Convert.ToInt32(item["Commodity_number"]);
                c.Commodity_Remark = (item["Commodity_Remark"]).ToString();
                c.Commodity_Weight = Convert.ToInt32(item["Commodity_Weight"]);
                c.Commodity_Price = Convert.ToInt32(item["Commodity_Price"]);
                c.CommodityType_Name = item["CommodityType_Name"].ToString();
                c.CommodityType_Id = Convert.ToInt32(item["CommodityType_Id"]);
                list.Add(c);
            }
            return list;
        }
        /// <summary>
        /// 获取商品类型
        /// </summary>
        /// <returns></returns>
        public List<CommodityType> GetCommodityTypeAll()
        {
            string sql = "select * from CommodityType";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connSql);
            DataTable table = new DataTable();
            adapter.Fill(table);
            List<CommodityType> list = new List<CommodityType>();
            foreach (DataRow item in table.Rows)
            {
                CommodityType c = new CommodityType();
                c.CommodityType_Id = Convert.ToInt32(item["CommodityType_Id"]);
                c.CommodityType_Name = (item["CommodityType_Name"]).ToString();
                list.Add(c);
            }
            return list;
        }
        /// <summary>
        /// 添加商品信息
        /// </summary>
        /// <param name="e"></param>
        /// <returns></returns>
        public int AddCommodity(Commodity c)
        {
            string ProcName = "Proc_AddCommodity";
            SqlParameter[] commandParameters = new SqlParameter[]
            {
                new SqlParameter("@Commodity_Name",c.Commodity_Name),
                new SqlParameter("@Commodity_number",c.Commodity_number),
                new SqlParameter("@Commodity_Weight",c.Commodity_Price),
                new SqlParameter("@Commodity_Remark",c.Commodity_Remark),
                new SqlParameter("@CommodityType_Id",c.CommodityType_Id),
                new SqlParameter("@Commodity_Price",c.Commodity_Price)
            };
            return p.ExecNonquery_Proc(ProcName, commandParameters);
        }
        /// <summary>
        /// 删除商品信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelCommodity(int id)
        {
            string sql = string.Format("delete from commodity where Commodity_Id='" + id + "'");
            return p.ExecNonquery(sql);
        }
        /// <summary>
        /// 根据id获取单行数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Commodity GetCommodityById(int id)
        {
            string sql = "select ct.CommodityType_Id,Commodity_Id,Commodity_Name,Commodity_number,Commodity_Weight,Commodity_Remark,ct.CommodityType_Name,Commodity_Weight,Commodity_Price from Commodity c join CommodityType ct on c.CommodityType_Id=ct.CommodityType_Id where Commodity_Id='" + id + "'";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connSql);
            DataTable table = new DataTable();
            adapter.Fill(table);
            Commodity c = new Commodity();
            foreach (DataRow item in table.Rows)
            {
                c.Commodity_Id = Convert.ToInt32(item["Commodity_Id"]);
                c.Commodity_Name = (item["Commodity_Name"]).ToString();
                c.Commodity_number = Convert.ToInt32(item["Commodity_number"]);
                c.Commodity_Remark = (item["Commodity_Remark"]).ToString();
                c.Commodity_Price = Convert.ToInt32(item["Commodity_Price"]);
                c.CommodityType_Name = item["CommodityType_Name"].ToString();
                c.CommodityType_Id = Convert.ToInt32(item["CommodityType_Id"]);
                c.Commodity_Weight = Convert.ToInt32(item["Commodity_Weight"]);
            }
            return c;
        }
        /// <summary>
        /// 更新商品信息
        /// </summary>
        /// <param name="c"></param>
        /// <returns></returns>
        public int EditCommodity(Commodity c)
        {
            string sql = string.Format("update Commodity set Commodity_Name='" + c.Commodity_Name + "',Commodity_number=" + c.Commodity_number + " ,Commodity_Price=" + c.Commodity_Price + ",Commodity_Remark='" + c.Commodity_Remark + "',Commodity_Weight='"+c.Commodity_Weight+"',CommodityType_Id=" + c.CommodityType_Id + " where Commodity_Id=" + c.Commodity_Id + "");
            return p.ExecNonquery(sql);
        }
        /// <summary>
        /// 根据商品名称查询商品信息
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<Commodity> SearchByName(string name)
        {
            string sql = string.Format("select Commodity_Id,Commodity_Name,Commodity_number,Commodity_Weight,Commodity_Remark,ct.CommodityType_Name,Commodity_Price from Commodity c join CommodityType ct on c.CommodityType_Id=ct.CommodityType_Id where Commodity_Name like '%" + name + "%'");
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connSql);
            DataTable table = new DataTable();
            adapter.Fill(table);
            List<Commodity> list = new List<Commodity>();
            foreach (DataRow item in table.Rows)
            {
                Commodity c = new Commodity();
                c.Commodity_Id = Convert.ToInt32(item["Commodity_Id"]);
                c.Commodity_Name = (item["Commodity_Name"]).ToString();
                c.Commodity_number = Convert.ToInt32(item["Commodity_number"]);
                c.Commodity_Remark = (item["Commodity_Remark"]).ToString();
                c.Commodity_Price = Convert.ToInt32(item["Commodity_Weight"]);
                c.CommodityType_Name = item["CommodityType_Name"].ToString();
                c.Commodity_Price = Convert.ToInt32(item["Commodity_Price"]);
                list.Add(c);
            }
            return list;
        }
        /// <summary>
        /// 搜索
        /// </summary>
        /// <param name="num"></param>
        /// <returns></returns>
        public DataTable Search(int num)
        {
            string sql = "pro_sel";
            SqlParameter[] parameter = new SqlParameter[1]
                                    {
                                          new SqlParameter("@num",num)
                                    };
            return p.ExecDataTable_Proc(sql,parameter);
        }
    }
}
